BigQuery でサポートされている疑似列を調べてみた

エノカワ

2023.03.31

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは！エノカワです。

今回は、BigQuery でサポートされている疑似列について実際に試してみたクエリとともに、ご紹介します。

疑似列とは

実際のテーブルやデータセットには存在しないが、クエリの中で参照できる特別な列です。
疑似列を使用することで、データの操作や分析がより柔軟かつ効率的に行えます。

BigQuery ではどういった疑似列がサポートされているのでしょうか？

気になって調べてみたところ、以下の疑似列がサポートされていました。
(2023年3月時点）

_FILE_NAME
ソースファイル名の取得
_PARTITIONDATE / _PARTITIONTIME
分割テーブルの分割基準の取得
_TABLE_SUFFIX
ワイルドカードテーブルでマッチしたテーブルのサフィックス取得

_FILE_NAME

外部テーブルをクエリする際に元のソースファイル名を取得するために使用されます。
データがファイルロードジョブや外部テーブルからインポートされた場合に利用可能です。

外部テーブルを作成して、ファイル名の取得、ファイルごとの集計、ファイル名でフィルタリングを試してみましょう。

【準備】外部テーブル作成

## 公開データセット shakespeare のデータを GCS にエクスポート
# 全データ
EXPORT DATA OPTIONS (
  uri = 'gs://cm_enokawa_work/shakespeare_export/all/*.csv',
  format = 'CSV'
) AS (
  SELECT
    *
  FROM
    `bigquery-public-data.samples.shakespeare`
);
# word_count = 1 のデータ
EXPORT DATA OPTIONS (
  uri = 'gs://cm_enokawa_work/shakespeare_export/wc1/*.csv',
  format = 'CSV'
) AS (
  SELECT
    *
  FROM
    `bigquery-public-data.samples.shakespeare`
  WHERE
    word_count = 1
);

## 外部テーブルを作成
CREATE EXTERNAL TABLE work.shakespeare_external
(
  word STRING,
  word_count INT64,
  corpus STRING,
  corpus_date INT64
)
OPTIONS (
  format="CSV",
  uris=["gs://cm_enokawa_work/shakespeare_export/*.csv"]
);

ファイル名を取得

SELECT
  _FILE_NAME AS file_name,
  word
FROM
  work.shakespeare_external
LIMIT 10

ファイルごとの集計

SELECT
  _FILE_NAME AS file_name,
  COUNT(*) AS record_count
FROM
  work.shakespeare_external
GROUP BY
  _FILE_NAME
ORDER BY
  _FILE_NAME

ファイル名でフィルタリング

SELECT
  _FILE_NAME AS file_name,
  word
FROM
  work.shakespeare_external
WHERE
  _FILE_NAME LIKE '%/wc1/%'
LIMIT 10

_FILE_NAME疑似列でデータの出典となるファイルを参照できるので、データ品質の問題や異常値の原因を特定するといったユースケースで利用できそうです。

下記エントリでは、_FILE_NAME疑似列を使った事例が紹介されていますので、こちらも是非ご参照ください！

_PARTITIONDATE / _PARTITIONTIME

分割テーブルの分割基準となる日付または時刻を取得するために使用されます。
これを使って特定の分割範囲に対するクエリを効率化することができます。

分割テーブルを作成して、取り込み時間でフィルタリング、取り込み時間ごとの集計を試してみましょう。

【準備】分割テーブル作成

先ほどGCS にエクスポートしたshakespeareのデータを取り込むテーブルを作成します。
[パーティションとクラスタの設定] セクションの [パーティショニング] で、[取り込み時間により分割] を選択します。
これにより、取り込み時間パーティショニング分割テーブルとして作成されます。

取り込み時間でフィルタリング

SELECT
  _PARTITIONTIME AS partition_timestamp,
  word
FROM
  work.shakespeare_partition
WHERE
  _PARTITIONTIME >= TIMESTAMP("2023-03-26")
LIMIT 10

取り込み時間ごとの集計

SELECT
  _PARTITIONTIME AS partition_timestamp,
  COUNT(*) AS record_count
FROM
  work.shakespeare_partition
GROUP BY
  partition_timestamp
ORDER BY
  partition_timestamp

_PARTITIONDATE

_PARTITIONDATE疑似列は、日単位のパーティション分割テーブルで選択できます。
[パーティションとクラスタの設定] セクションの [パーティショニングタイプ] で、[1 日ごと] を選択します。
これにより、日単位のパーティション分割テーブルとして作成されます。

SELECT
  _PARTITIONDATE AS partition_date,
  _PARTITIONTIME AS partition_timestamp,
  word
FROM
  work.shakespeare_partition_day
WHERE
  _PARTITIONDATE = '2023-03-26'
LIMIT 10

_PARTITIONDATE / _PARTITIONTIME疑似列で取り込み時間を参照できるので、分析対象期間を絞り込んでクエリのパフォーマンスを向上させるといったユースケースで利用できそうです。

下記エントリでは、_PARTITIONTIME疑似列を使った事例が紹介されていますので、こちらも是非ご参照ください！

_TABLE_SUFFIX

ワイルドカードテーブルを使って複数のテーブルをクエリする際に、マッチしたテーブルのサフィックス（接尾辞）を取得するために使用されます。
サフィックスは、プレフィックスと一緒に使用され、テーブル名を一意に識別します。

シャーディングされたテーブルで、テーブルの範囲を制限、テーブルごとの集計を試してみましょう。

【準備】シャーディングされたテーブル

公開データセットのシャーディングされたテーブルnoaa_gsod.gsodを使用します。

テーブルの範囲を制限

SELECT
  _TABLE_SUFFIX AS table_suffix,
  year,
  mo,
  da
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  _TABLE_SUFFIX = '2023'
LIMIT 10

テーブルごとの集計

SELECT
  _TABLE_SUFFIX AS year,
  COUNT(*) AS record_count
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  _TABLE_SUFFIX BETWEEN '2010' AND '2020'
GROUP BY
  year
ORDER BY
  year